﻿if exists (select 1
          from sysobjects
          where  id = object_id('bd_proc_report_overtime_detail')
          and type in ('P','PC'))
   drop procedure bd_proc_report_overtime_detail
go


create procedure bd_proc_report_overtime_detail (@date_begin DATE,@date_end DATE,@userNameOrNo VARCHAR(100),@users VARCHAR(MAX),@depts VARCHAR(MAX),@user_type VARCHAR(50),@user_company VARCHAR(200),@user_status VARCHAR(20),@is_page INT = 1,@page_size INT = 50,@page_no INT = 1,@row_count INT = 0 OUTPUT) as
BEGIN
  SET NOCOUNT ON;
--工号、姓名、员工类型、所属公司、工作状态
  CREATE TABLE #result
  (
    user_serial BIGINT
    , user_no VARCHAR(20)
    , user_name VARCHAR(200) COLLATE Chinese_PRC_CI_AS
    , dept_id BIGINT
    , dept_name VARCHAR(500) COLLATE Chinese_PRC_CI_AS
    , card_no VARCHAR(50) COLLATE Chinese_PRC_CI_AS --员工卡号
    , card_manhour VARCHAR(50) COLLATE Chinese_PRC_CI_AS  --员工属性工时制
    , company VARCHAR(200) COLLATE Chinese_PRC_CI_AS    --员工属性所属公司
    , user_type VARCHAR(50) COLLATE Chinese_PRC_CI_AS   --员工属性员工类型
    , user_status VARCHAR(50) COLLATE Chinese_PRC_CI_AS  --员工属性工作状态

    , ot_detail_id  INT
    , order_id  INT
    , bill_no VARCHAR(100)    --加班单号
    , ot_applicant_no VARCHAR(50) --申请人工号
    , ot_applicant_name VARCHAR(100)  --申请人姓名
    , ot_date_apply DATETIME  --加班申请提交时间
    , ot_date DATE  --加班日期

    , ot_begin INT   --加班申请开始时间
    , ot_end INT     --加班申请结束时间
--    , ot_time_actual_begin  INT   --加班实际开始时间
--    , ot_time_actual_end  INT     --加班实际结束时间

    , ot_time_begin DATETIME   --加班申请开始时间
    , ot_time_end DATETIME     --加班申请结束时间

    , ot_time_actual_begin  DATETIME   --加班实际开始时间
    , ot_time_actual_end  DATETIME     --加班实际结束时间
		, status_name varchar(20) --审批状态名
  )

--	流程编号：申请单号
--	申请人：加班申请人姓名
--	申请日期：加班申请日期
--	加班日期：实际加班日期
--	加班申请开始时间：格式时分，如8:00
--	加班申请结束时间：格式时分，如16:30
--	加班实际开始时间：员工加班上班打卡时间，如果是连班，以班次下班时间为开始时间
--	加班实际结束时间：员工加班下班打卡时间

  DECLARE @sql VARCHAR(MAX) = ''

  SELECT @sql = '
    INSERT INTO #result(user_serial, user_no, user_name, dept_id, dept_name, card_no, card_manhour, company, user_type, user_status,
      ot_detail_id, order_id, bill_no, ot_applicant_no, ot_applicant_name, ot_date_apply,
      ot_date, ot_begin, ot_end, ot_time_begin, ot_time_end,status_name)
    SELECT ot.user_serial, u.user_no, u.user_lname, u.user_dep, u.dep_name, u.card_number, u.man_hour_system, u.user_company, u.user_sny_lx, u.user_sny_type,
      ot.detail_id, ot.order_id, ot.order_no, cu.user_no, cu.user_lname, ot.submitter_time, ot.ot_date, ot.ot_begin, ot.ot_end, ot.kssj, ot.jssj,ot.status_name
    FROM bd_view_overtime ot
    INNER JOIN bd_view_user_all u ON u.user_serial = ot.user_serial
    INNER JOIN (SELECT user_serial, user_no, user_lname FROM bd_view_user_all) cu ON cu.user_serial = ot.submitter_id
    WHERE ot.is_delete = 0 and  convert(varchar(10),ot.ot_date,120)>= '''+convert(varchar(10),@date_begin,120)
		+''' and convert(varchar(10),ot.ot_date,120) <='''+ convert(varchar(10),@date_end,120)+''''


  IF(ISNULL(@userNameOrNo, '') <> '' AND ISNULL(@users, '') = '')
  BEGIN
  	SELECT @sql += ' AND u.user_no LIKE ''%' + @userNameOrNo + '%'' OR u.user_lname LIKE ''%' + @userNameOrNo + '%'''
  END

  IF(ISNULL(@users, '') <> '')
  BEGIN
  	SELECT @sql += ' AND u.user_serial IN(' + @users + ')'
  END

  IF(ISNULL(@depts, '') <> '')
  BEGIN
  	SELECT @sql += ' AND u.user_dep IN(' + @depts + ')'
  END

  IF(ISNULL(@user_type, '') <> '')
  BEGIN
  	SELECT @sql += ' AND u.user_sny_lx = ''' + @user_type + ''''
  END

  IF(ISNULL(@user_company, '') <> '')
  BEGIN
  	SELECT @sql += ' AND u.user_company LIKE ''%' + @user_company + '%'''
  END

  IF(ISNULL(@user_status, '') <> '')
  BEGIN
  	SELECT @sql += ' AND u.user_sny_type = ''' + @user_status + ''''
  END

  EXEC(@sql)

  SELECT @row_count = COUNT(1) FROM #result

  UPDATE r
  SET ot_time_actual_begin = ot.ot_swipe_begin, ot_time_actual_end = ot.ot_swipe_end
  FROM #result r
  INNER JOIN bd_kq_overtime_actual_swipe ot ON ot.order_detail_id = r.ot_detail_id


  IF(ISNULL(@is_page, 0) = 0)
  BEGIN
  	SELECT
			user_serial, user_no, user_name, dept_id, dept_name, card_no, card_manhour, company, user_type, user_status, ot_detail_id, order_id,
			bill_no, ot_applicant_no, ot_applicant_name,
			convert(varchar(19),ot_date_apply,120) as ot_date_apply,
			convert(varchar(10),ot_date,120) as ot_date,
			ot_begin, ot_end,
			convert(varchar(16),ot_time_begin,120) as ot_time_begin,
			convert(varchar(16),ot_time_end,120) as ot_time_end,
			convert(varchar(16),ot_time_actual_begin,120) as ot_time_actual_begin,
			convert(varchar(16),ot_time_actual_end,120) as ot_time_actual_end,
			status_name
		FROM #result ORDER BY order_id DESC
  END ELSE
  BEGIN
    ;WITH result AS (
      SELECT *,row_no = ROW_NUMBER() OVER (ORDER BY ot_date_apply DESC) FROM #result
    )
    SELECT user_serial, user_no, user_name, dept_id, dept_name, card_no, card_manhour, company, user_type, user_status, ot_detail_id, order_id, bill_no, ot_applicant_no, ot_applicant_name,
		convert(varchar(19),ot_date_apply,120) as ot_date_apply,
		convert(varchar(10),ot_date,120) as ot_date,
		ot_begin, ot_end,
		convert(varchar(16),ot_time_begin,120) as ot_time_begin,
		convert(varchar(16),ot_time_end,120) as ot_time_end,
		convert(varchar(16),ot_time_actual_begin,120) as ot_time_actual_begin,
		convert(varchar(16),ot_time_actual_end,120) as ot_time_actual_end,
		status_name,row_no
    FROM result
    WHERE row_no BETWEEN (@page_no - 1) * @page_size + 1 AND @page_no * @page_size
  END

  DROP TABLE #result

  RETURN @row_count

  SET NOCOUNT OFF;
END
go
